# -*- coding: utf-8 -*-
from openai import OpenAI
from flask import Flask,render_template,request,jsonify,session,redirect
from flask_cors import CORS
import pymysql
import random
import string
import time
import smtplib
from email.mime.text import MIMEText
import math
from werkzeug.security import generate_password_hash, check_password_hash
from volcengine.visual.VisualService import VisualService


app = Flask(__name__)
app.secret_key='huiwen'
#snh{
app.config['SESSION_COOKIE_SECURE'] = False  # 如果使用 HTTPS
#snh}
app.config['SESSION_COOKIE_SAMESITE'] = 'Lax'  # 防止 CSRF
CORS(app)  # 启用 CORS
app.config['SECRET_KEY'] = 'huiwen'  # 用于 JWT 签名的密钥，需要保密
db_config = {
    'host': 'mysql2.sqlpub.com',  # 替换为 SQLHub 的主机地址
    'port': 3307,                  # SQLHub 端口号
    'user': 'shounahe',    # SQLHub 用户名
    'password': 'bHpSgMGWa452lCWx',  # SQLHub 密码
    'database': 'huiwen',          # SQLHub 中的数据库名
    'charset': 'utf8mb4',          # 指定字符集
    'cursorclass': pymysql.cursors.DictCursor  # 返回字典格式结果
}

# 邮件配置
EMAIL_CONFIG = {
    'sender': 'huiwen_ai@foxmail.com',  # 发件人邮箱
    'password': 'zpxumynhhcpadbab',  # 邮箱密码/授权码
    'smtp_server': 'smtp.qq.com',    # SMTP服务器地址
    'smtp_port': 465                 # SMTP端口
}

# UDP反馈服务器配置
UDP_FEEDBACK_SERVER = {
    'host': 'localhost',
    'port': 9999
}

# 验证码存储字典（实际生产环境应使用数据库或Redis）
verification_codes = {}

model = {
    'deepseek-V3': 'deepseek/deepseek-chat-v3-0324:free',
    'deepseek-R1': 'deepseek/deepseek-r1-0528-qwen3-8b:free',
    'Mistral': 'mistralai/devstral-small:free',
    'Gemma-3n': 'google/gemma-3n-e4b-it:free',
    'Llama-3': 'meta-llama/llama-3.3-8b-instruct:free',
    'Phi-4': 'microsoft/phi-4-reasoning-plus:free',
    'Qwen3': 'qwen/qwen3-30b-a3b:free',
    'Kimi': 'moonshotai/kimi-vl-a3b-thinking:free'
}

def get_model_response(model_id, prompt):
    client = OpenAI(
        base_url="https://openrouter.ai/api/v1",
        api_key="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",
    )
    completion = client.chat.completions.create(
        extra_body={},
        model=model[model_id],
        messages=[{"role": "user", "content": prompt}],
    )
    print(completion.choices[0].message.content)
    return completion.choices[0].message.content

def get_img_output(prompt,seed,width,height):
    visual_service = VisualService()
    visual_service.set_ak('AKLTNmNlZDUzZDZjYjFhNGNlZThkYzE1ZDI0NTI2NDJmNjQ')
    visual_service.set_sk('TkdWaFlqTmhZVFF4TldReU5HUXhObUV6Tnpjek5qUXdabUU1TlRGa05Eaw==')

    form = {
        "req_key": "high_aes_general_v30l_zt2i",
        "prompt": prompt,
        "seed": seed,
        "scale": 3.5,
        "width": width,
        "height": height,
        "return_url": True,
        "logo_info": {
            "add_logo": True,
            "position": 0,
            "language": 0,
            "opacity": 0.3,
            "logo_text_content": "汇问"
        }
    }
    return visual_service.cv_process(form)

# ================== 日志功能 ==================
def insert_access_log(user_id, username, action, ip):
    try:
        connection = pymysql.connect(**db_config)
        with connection.cursor() as cursor:
            sql = "INSERT INTO user_access_log (user_id, username, action, ip) VALUES (%s, %s, %s, %s)"
            cursor.execute(sql, (user_id, username, action, ip))
        connection.commit()
    except Exception as e:
        print("插入访问日志失败: {}".format(e))
    finally:
        if 'connection' in locals():
            connection.close()


def insert_system_log(level, user_id, username, action, message, ip):
    try:
        connection = pymysql.connect(**db_config)
        with connection.cursor() as cursor:
            sql = "INSERT INTO system_log (level, user_id, username, action, message, ip) VALUES (%s, %s, %s, %s, %s, %s)"
            cursor.execute(sql, (level, user_id, username, action, message, ip))
        connection.commit()
    except Exception as e:
        print("插入系统日志失败: {}".format(e))
    finally:
        if 'connection' in locals():
            connection.close()



#snh{
# 修改 /search 路由
@app.route('/search')
def search():
    if 'logged_in' not in session or not session['logged_in']:
        return redirect('/login')

    # 如果是管理员，重定向到管理界面
    if session.get('user_type') == '管理员':
        return redirect('/admin')

    return render_template('index.html')
#snh}
@app.route('/chat', methods=['POST'])
def chat():
    data = request.json
    prompt = data.get('prompt')
    print(prompt)
    selected_models = data.get('model')
    if not prompt:
        return jsonify({"success": False, "error": "Missing prompt"}), 400
    if not selected_models:
        selected_models=['deepseek-V3']
    combined_response = ""
    for model_id in selected_models:
        if model_id in model:
            try:
                response = get_model_response(model_id, prompt)
                combined_response += "## {}:\n{}\n\n".format(model_id, response)
            except Exception as e:
                combined_response += "## {} Error:\n{}\n\n".format(model_id, str(e))

    return jsonify({
        "success": True,
        "reply": combined_response
    })

@app.route('/workbench',methods=['get','post'])
def workbench():
    if 'logged_in' not in session or not session['logged_in']:
        return redirect('/login')
    return render_template('workbench.html')

@app.route('/feedback')
def feedback():
    if 'logged_in' not in session or not session['logged_in']:
        return redirect('/login')
    return render_template('feedback.html')

@app.route('/image',methods=['get','post'])
def image():
    data=request.json
    prompt=data.get('prompt')
    model_first=data.get('first')
    seed=int(data.get('seed'))
    width=int(data.get('width'))
    height=int(data.get('height'))
    print(prompt,model_first,seed,width,height)
    text=get_model_response(model_first,prompt)
    print(text)
    img=get_img_output(text,seed,width,height)
    print(img)
    return jsonify({
        'success':True,
        'text':text,
        'img':img.get('data').get('image_urls')[0]
    })


@app.route('/')
def init():
    return render_template('init.html')

# 注册页面路由
@app.route('/register')
def register_page():
    return render_template('register.html')

# 登录页面路由
@app.route('/login')
def login_page():
    return render_template('login.html')

@app.route('/api/register', methods=['POST', 'OPTIONS'])
def register():
    if request.method == 'OPTIONS':
        return jsonify({'message': 'CORS preflight request successful'}), 200

    data = request.get_json()
    username = data.get('username')
    email = data.get('email')
    password = data.get('password')
    verificationCode = data.get('verificationCode')

    if not username or not email or not password or not verificationCode:
        return jsonify({'message': '缺少必填字段'}), 400

    # 验证验证码逻辑
    if email in verification_codes:
        code_info = verification_codes[email]
        if time.time() > code_info['expire']:
            return jsonify({'message': '验证码已过期'}), 400
        if verificationCode != code_info['code']:
            return jsonify({'message': '验证码错误'}), 400
        del verification_codes[email]
    else:
        return jsonify({'message': '请先获取验证码'}), 400

    try:
        # 生成密码哈希
        hashed_password = generate_password_hash(password)

        connection = pymysql.connect(**db_config)
        with connection.cursor() as cursor:
            sql = "INSERT INTO users (username, email, password) VALUES (%s, %s, %s)"
            cursor.execute(sql, (username, email, hashed_password))
        connection.commit()

        # 记录注册成功日志
        ip = request.remote_addr
        insert_system_log(
            'INFO',
            0,  # 系统操作
            'system',
            'register',
            '新用户注册: {}({})'.format(username, email),
            ip
        )

        return jsonify({'message': '注册成功'})
    except pymysql.err.IntegrityError as e:
        if 'Duplicate entry' in str(e):
            if 'username' in str(e):
                return jsonify({'message': '用户名已存在'}), 400
            elif 'email' in str(e):
                return jsonify({'message': '邮箱已存在'}), 400
    except Exception as e:
        print("错误: {}".format(e))
        return jsonify({'message': '服务器错误'}), 500
    finally:
        if 'connection' in locals():
            connection.close()


# 修改登录API - 添加会话设置
@app.route('/api/login', methods=['POST', 'OPTIONS'])
def login():
    if request.method == 'OPTIONS':
        return jsonify({'message': 'CORS preflight request successful'}), 200
    data = request.get_json()
    username = data.get('username')
    password = data.get('password')
    if not username or not password:
        return jsonify({'message': '缺少必填字段'}), 400
    try:
        connection = pymysql.connect(**db_config)
        with connection.cursor() as cursor:
            sql = "SELECT id, username, password, type FROM users WHERE username = %s"
            cursor.execute(sql, (username,))
            user = cursor.fetchone()
        if not user:
            return jsonify({'message': '用户名或密码错误'}), 400
        if not check_password_hash(user['password'], password):
            return jsonify({'message': '用户名或密码错误'}), 400
        session['user_id'] = user['id']
        session['username'] = user['username']
        session['logged_in'] = True
        session['user_type'] = user['type']
        redirect_url = '/admin' if user['type'] == '管理员' else '/search'
        # 新增：插入访问日志和系统日志
        ip = request.remote_addr
        insert_access_log(user['id'], user['username'], 'login', ip)
        insert_system_log('INFO', user['id'], user['username'], 'login', '用户登录', ip)
        return jsonify({
            'message': '登录成功',
            'redirect': redirect_url,
            'user': {
                'id': user['id'],
                'username': user['username'],
                'type': user['type']
            }
        })
    except Exception as e:
        print("错误: {}".format(e))
        return jsonify({'message': '服务器错误', 'error': str(e)}), 500
    finally:
        if 'connection' in locals():
            connection.close()


# 发送验证码函数
def send_verification_email(email, code):
    server = None
    try:
        # 邮件内容
        subject = "汇问注册验证码"
        body = "您的注册验证码是：<b>{}</b><br>该验证码5分钟内有效，请勿泄露给他人。".format(code)

        # 创建邮件对象
        msg = MIMEText(body, 'html')
        msg['Subject'] = subject
        msg['From'] = EMAIL_CONFIG['sender']
        msg['To'] = email

        # 发送邮件 - 使用SMTP_SSL
        server = smtplib.SMTP_SSL(EMAIL_CONFIG['smtp_server'], EMAIL_CONFIG['smtp_port'])
        server.login(EMAIL_CONFIG['sender'], EMAIL_CONFIG['password'])
        server.sendmail(EMAIL_CONFIG['sender'], [email], msg.as_string())
        print("邮件已成功发送至: {}".format(email))
        return True
    except smtplib.SMTPException as e:
        print("SMTP发送邮件失败: {}".format(e))
        return False
    except Exception as e:
        print("发送邮件出现意外错误: {}".format(e))
        return False
    finally:
        if server:
            try:
                server.quit()  # 更优雅地关闭连接
            except:
                pass  # 忽略退出时的错误


# 修改验证码发送API
@app.route('/api/send_verification_code', methods=['POST', 'OPTIONS'])
def send_verification_code():
    if request.method == 'OPTIONS':
        return jsonify({'message': 'CORS preflight request successful'}), 200

    data = request.get_json()
    email = data.get('email')

    if not email:
        return jsonify({'message': '邮箱不能为空'}), 400

    try:
        # 检查邮箱是否已注册
        connection = pymysql.connect(**db_config)
        with connection.cursor() as cursor:
            sql = "SELECT email FROM users WHERE email = %s"
            cursor.execute(sql, (email,))
            if cursor.fetchone():
                return jsonify({'message': '该邮箱已注册'}), 400
    except Exception as e:
        print("数据库错误: {}".format(e))
        return jsonify({'message': '服务器错误'}), 500
    finally:
        if 'connection' in locals():
            connection.close()

    # 生成6位随机验证码
    code = ''.join(random.choices(string.digits, k=6))
    timestamp = time.time()

    # 保存验证码（有效期5分钟）
    verification_codes[email] = {
        'code': code,
        'expire': timestamp + 300  # 5分钟有效期
    }

    # 发送邮件（不再检查发送结果）
    try:
        send_verification_email(email, code)
        print("验证码: {}".format(code))  # 开发环境记录
        return jsonify({'message': '验证码已发送'})
    except Exception as e:
        print("发送过程中出现异常: {}".format(e))
        print("验证码: {}".format(code))
        return jsonify({'message': '验证码已发送'})

@app.route('/classification')
def classification():
    if 'logged_in' not in session or not session['logged_in']:
        return redirect('/login')
    return render_template('aiclassification.html')

# 添加登出路由
@app.route('/api/logout', methods=['POST'])
def logout():
    # 记录登出日志
    if 'logged_in' in session and session['logged_in']:
        ip = request.remote_addr
        insert_access_log(
            session['user_id'],
            session['username'],
            'logout',
            ip
        )
        insert_system_log(
            'INFO',
            session['user_id'],
            session['username'],
            'logout',
            '用户登出',
            ip
        )

    session.clear()  # 清除会话
    return jsonify({
        'message': '登出成功',
        'redirect': '/login'  # 重定向到登录页
    })

# 添加检查登录状态的路由
# 修改 /api/check_auth 路由
@app.route('/api/check_auth')
def check_auth():
    if 'logged_in' in session and session['logged_in']:
        return jsonify({
            'logged_in': True,
            'user_id': session.get('user_id'),
            'username': session.get('username'),
            'user_type': session.get('user_type')
        })
    return jsonify({'logged_in': False})

#snh{
# 修改 /admin 路由
@app.route('/admin')
def admin():
    if 'logged_in' not in session or not session['logged_in']:
        return redirect('/login')

    # 如果不是管理员，重定向到普通用户界面
    if session.get('user_type') != '管理员':
        return redirect('/search')

    # 新增：插入访问日志
    ip = request.remote_addr
    insert_access_log(session['user_id'], session['username'], 'visit_admin', ip)
    insert_system_log(
        'INFO',
        session['user_id'],
        session['username'],
        'visit_admin',
        '访问管理员页面',
        ip
    )

    return render_template('admin_index.html')

###################################################

# 系统管理路由
@app.route('/system')
def system_management():
    if 'logged_in' not in session or not session['logged_in']:
        return redirect('/login')
    if session.get('user_type') != '管理员':
        return redirect('/search')
    # 新增：插入访问日志
    ip = request.remote_addr
    insert_access_log(session['user_id'], session['username'], 'visit_system', ip)
    insert_system_log(
        'INFO',
        session['user_id'],
        session['username'],
        'visit_system',
        '访问系统管理页面',
        ip
    )
    return render_template('system.html')
######################################################



@app.route('/api/admin/users', methods=['GET'])  # 假设使用 GET 方法获取用户列表
def get_users():
    if 'logged_in' not in session or not session['logged_in']:
        return jsonify({'message': '未登录'}), 401
    if session.get('user_type') != '管理员':
        return jsonify({'message': '无权限访问'}), 403

    try:
        connection = pymysql.connect(**db_config)
        with connection.cursor() as cursor:
            sql = "SELECT id, username, email, type FROM users"
            cursor.execute(sql)
            users = cursor.fetchall()
        connection.commit()
        return jsonify({'success': True, 'users': users})
    except Exception as e:
        print("错误: {}".format(e))
        return jsonify({'success': False, 'message': '服务器错误', 'error': str(e)}), 500
    finally:
        if 'connection' in locals():
            connection.close()

@app.route('/api/admin/users', methods=['POST'])
def add_user():
    if 'logged_in' not in session or not session['logged_in']:
        return jsonify({'message': '未登录'}), 401
    if session.get('user_type') != '管理员':
        return jsonify({'message': '无权限访问'}), 403

    data = request.get_json()
    username = data.get('username')
    email = data.get('email')
    password = data.get('password')
    role = data.get('role', 'user')

    if not username or not email or not password:
        return jsonify({'message': '缺少必填字段'}), 400

    try:
        hashed_password = generate_password_hash(password)
        user_type = '管理员' if role == 'admin' else '普通用户'

        connection = pymysql.connect(**db_config)
        with connection.cursor() as cursor:
            sql = "INSERT INTO users (username, email, password, type) VALUES (%s, %s, %s, %s)"
            cursor.execute(sql, (username, email, hashed_password, user_type))
        connection.commit()

        # 记录系统日志
        ip = request.remote_addr
        insert_system_log(
            'INFO',
            session['user_id'],
            session['username'],
            'add_user',
            '添加用户: {}({})'.format(username, email),
            ip
        )

        return jsonify({'success': True, 'message': '用户添加成功'})
    except pymysql.err.IntegrityError as e:
        if 'Duplicate entry' in str(e):
            if 'username' in str(e):
                return jsonify({'success': False, 'message': '用户名已存在'}), 400
            elif 'email' in str(e):
                return jsonify({'success': False, 'message': '邮箱已存在'}), 400
    except Exception as e:
        print("错误: {}".format(e))
        return jsonify({'success': False, 'message': '服务器错误'}), 500
    finally:
        if 'connection' in locals():
            connection.close()


@app.route('/api/admin/users/<int:user_id>', methods=['PUT'])
def update_user(user_id):
    if 'logged_in' not in session or not session['logged_in']:
        return jsonify({'message': '未登录'}), 401
    if session.get('user_type') != '管理员':
        return jsonify({'message': '无权限访问'}), 403

    data = request.get_json()
    username = data.get('username')
    email = data.get('email')
    password = data.get('password')
    role = data.get('role', 'user')

    if not username or not email:
        return jsonify({'message': '缺少必填字段'}), 400

    try:
        user_type = '管理员' if role == 'admin' else '普通用户'
        update_fields = []
        params = []

        if username:
            update_fields.append("username = %s")
            params.append(username)
        if email:
            update_fields.append("email = %s")
            params.append(email)
        if password:
            hashed_password = generate_password_hash(password)
            update_fields.append("password = %s")
            params.append(hashed_password)
        if role:
            update_fields.append("type = %s")
            params.append(user_type)

        params.append(user_id)

        connection = pymysql.connect(**db_config)
        with connection.cursor() as cursor:
            sql = "UPDATE users SET {} WHERE id = %s".format(', '.join(update_fields))
            cursor.execute(sql, params)
        connection.commit()

        # 记录系统日志
        ip = request.remote_addr
        insert_system_log(
            'INFO',
            session['user_id'],
            session['username'],
            'update_user',
            '更新用户ID: {}'.format(user_id),
            ip
        )

        return jsonify({'success': True, 'message': '用户更新成功'})
    except pymysql.err.IntegrityError as e:
        if 'Duplicate entry' in str(e):
            if 'username' in str(e):
                return jsonify({'success': False, 'message': '用户名已存在'}), 400
            elif 'email' in str(e):
                return jsonify({'success': False, 'message': '邮箱已存在'}), 400
    except Exception as e:
        print("错误: {}".format(e))
        return jsonify({'success': False, 'message': '服务器错误'}), 500
    finally:
        if 'connection' in locals():
            connection.close()

@app.route('/api/admin/users/<int:user_id>', methods=['DELETE'])
def delete_user(user_id):
    if 'logged_in' not in session or not session['logged_in']:
        return jsonify({'message': '未登录'}), 401
    if session.get('user_type') != '管理员':
        return jsonify({'message': '无权限访问'}), 403

    try:
        connection = pymysql.connect(**db_config)
        with connection.cursor() as cursor:
            # 获取即将删除的用户信息
            sql_select = "SELECT username, email FROM users WHERE id = %s"
            cursor.execute(sql_select, (user_id,))
            user_info = cursor.fetchone()

            sql_delete = "DELETE FROM users WHERE id = %s"
            cursor.execute(sql_delete, (user_id,))
        connection.commit()

        # 记录系统日志
        if user_info:
            ip = request.remote_addr
            insert_system_log(
                'WARNING',
                session['user_id'],
                session['username'],
                'delete_user',
                '删除用户: {}({})'.format(user_info["username"], user_info["email"]),
                ip
            )

        return jsonify({'success': True, 'message': '用户删除成功'})
    except Exception as e:
        print("错误: {}".format(e))
        return jsonify({'success': False, 'message': '服务器错误'}), 500
    finally:
        if 'connection' in locals():
            connection.close()


# ================== 系统管理API ==================##############################
@app.route('/api/system/traffic')
def system_traffic():
    try:
        connection = pymysql.connect(**db_config)
        with connection.cursor() as cursor:
            sql = """
                SELECT DATE_FORMAT(timestamp, '%m-%d') as day, COUNT(*) as cnt
                FROM user_access_log
                WHERE action = 'login' AND timestamp >= NOW() - INTERVAL 30 DAY
                GROUP BY day
                ORDER BY day
            """
            cursor.execute(sql)
            rows = cursor.fetchall()
        x = [row['day'] for row in rows]
        y = [row['cnt'] for row in rows]
        return jsonify({'success': True, 'data': {'x': x, 'y': y}})
    except Exception as e:
        print("错误: {}".format(e))
        return jsonify({'success': False, 'msg': str(e)})
    finally:
        if 'connection' in locals():
            connection.close()


@app.route('/api/system/online')
def system_online():
    try:
        connection = pymysql.connect(**db_config)
        with connection.cursor() as cursor:
            sql = """
                SELECT COUNT(DISTINCT user_id) as online_count
                FROM user_access_log
                WHERE timestamp >= NOW() - INTERVAL 5 MINUTE
            """
            cursor.execute(sql)
            row = cursor.fetchone()
        return jsonify({'success': True, 'data': {'x': ['当前在线'], 'y': [row['online_count']]}})
    except Exception as e:
        print("错误: {}".format(e))
        return jsonify({'success': False, 'msg': str(e)})
    finally:
        if 'connection' in locals():
            connection.close()


@app.route('/api/system/logs', methods=['GET', 'DELETE'])
def system_logs_api():
    if request.method == 'GET':
        # 分页参数
        page = int(request.args.get('page', 1))
        page_size = int(request.args.get('page_size', 20))
        keyword = request.args.get('keyword', '').strip()
        try:
            connection = pymysql.connect(**db_config)
            with connection.cursor() as cursor:
                base_sql = "SELECT * FROM system_log"
                where = []
                params = []
                if keyword:
                    where.append("(username LIKE %s OR action LIKE %s OR message LIKE %s)")
                    params += ["%{}%".format(keyword)] * 3
                if where:
                    base_sql += " WHERE " + " AND ".join(where)
                base_sql += " ORDER BY timestamp DESC LIMIT %s OFFSET %s"
                params += [page_size, (page - 1) * page_size]
                cursor.execute(base_sql, params)
                logs = cursor.fetchall()
                # 总数
                count_sql = "SELECT COUNT(*) as cnt FROM system_log"
                if where:
                    count_sql += " WHERE " + " AND ".join(where)
                cursor.execute(count_sql, params[:-2])
                total = cursor.fetchone()['cnt']
            return jsonify({'success': True, 'logs': logs, 'total': total, 'page': page, 'page_size': page_size,
                            'pages': math.ceil(total / page_size)})
        except Exception as e:
            print("错误: {}".format(e))
            return jsonify({'success': False, 'msg': str(e)})
        finally:
            if 'connection' in locals():
                connection.close()
    elif request.method == 'DELETE':
        try:
            connection = pymysql.connect(**db_config)
            with connection.cursor() as cursor:
                cursor.execute("TRUNCATE TABLE system_log")
            connection.commit()

            # 记录日志清空操作
            ip = request.remote_addr
            insert_system_log(
                'WARNING',
                session['user_id'],
                session['username'],
                'clear_logs',
                '清空系统日志',
                ip
            )

            return jsonify({'success': True, 'message': '日志已清空'})
        except Exception as e:
            print("错误: {}".format(e))
            return jsonify({'success': False, 'msg': str(e)})
        finally:
            if 'connection' in locals():
                connection.close()


# 日志导出接口
@app.route('/api/system/logs/export')
def export_logs():
    try:
        connection = pymysql.connect(**db_config)
        with connection.cursor() as cursor:
            cursor.execute("SELECT * FROM system_log ORDER BY timestamp DESC")
            logs = cursor.fetchall()
        content = ''
        for log in logs:
            content += "[{}] [{}] [{}] [{}] {} (IP:{})\n".format(log['timestamp'], log['level'], log['username'], log['action'], log['message'], log['ip'])

        # 记录导出操作
        ip = request.remote_addr
        insert_system_log(
            'INFO',
            session['user_id'],
            session['username'],
            'export_logs',
            '导出系统日志',
            ip
        )

        return content, 200, {'Content-Type': 'text/plain; charset=utf-8',
                              'Content-Disposition': 'attachment; filename=system.log'}
    except Exception as e:
        return str(e), 500
    finally:
        if 'connection' in locals():
            connection.close()

############################################################

# ================== 反馈系统API ==================

@app.route('/api/feedback/submit', methods=['POST'])
def submit_feedback():
    """处理用户反馈提交
    - 验证用户登录状态
    - 从session获取用户信息
    - 将反馈数据存储到数据库
    - 记录系统日志
    - 返回操作结果
    """
    # 验证用户登录状态
    if 'logged_in' not in session or not session['logged_in']:
        return jsonify({'error': '未登录'}), 401

    try:
        data = request.json  # 获取JSON格式的请求数据
        user_id = session.get('user_id')  # 从session获取用户ID
        username = session.get('username')  # 从session获取用户名

        # 连接数据库
        connection = pymysql.connect(**db_config)
        with connection.cursor() as cursor:
            # 插入反馈到数据库
            sql = """
                INSERT INTO user_feedback (user_id, username, feedback_type, title, content)
                VALUES (%s, %s, %s, %s, %s)
            """
            cursor.execute(sql, (
                user_id,
                username,
                data.get('type'),  # 反馈类型
                data.get('title'),  # 反馈标题
                data.get('content')  # 反馈内容
            ))
        connection.commit()  # 提交事务
        connection.close()  # 关闭连接

        # 记录系统日志（安全审计）
        ip = request.remote_addr  # 获取客户端IP
        insert_system_log(
            'INFO',  # 日志级别
            user_id,  # 操作用户ID
            username,  # 操作用户名
            'feedback_submit',  # 操作类型
            '用户提交反馈: {}'.format(data.get("title")),  # 操作描述
            ip  # 客户端IP
        )

        return jsonify({'success': True, 'message': '反馈提交成功'})

    except Exception as e:
        # 异常处理
        return jsonify({'error': '提交失败: {}'.format(str(e))}), 500


# 用户反馈历史查询
@app.route('/api/feedback/history')
def get_feedback_history():
    """获取当前用户的反馈历史
    - 验证用户登录状态
    - 从数据库查询该用户的所有反馈
    - 按创建时间降序排列
    - 返回JSON格式的反馈列表
    """
    if 'logged_in' not in session or not session['logged_in']:
        return jsonify({'error': '未登录'}), 401

    try:
        user_id = session.get('user_id')  # 从session获取用户ID

        # 连接数据库
        connection = pymysql.connect(**db_config)
        with connection.cursor() as cursor:
            # 查询用户所有反馈
            sql = """
                SELECT * FROM user_feedback 
                WHERE user_id = %s 
                ORDER BY created_at DESC  # 按创建时间倒序排列
            """
            cursor.execute(sql, (user_id,))
            feedbacks = cursor.fetchall()  # 获取所有结果
        connection.close()

        return jsonify(feedbacks)  # 直接返回查询结果

    except Exception as e:
        return jsonify({'error': '获取失败: {}'.format(str(e))}), 500


# 管理员获取所有反馈
@app.route('/api/feedback/admin/list')
def get_all_feedback():
    """管理员获取所有用户反馈
    - 验证管理员权限
    - 查询所有反馈记录
    - 按创建时间降序排列
    """
    # 验证登录状态
    if 'logged_in' not in session or not session['logged_in']:
        return jsonify({'error': '未登录'}), 401

    # 验证管理员权限
    if session.get('user_type') != '管理员':
        return jsonify({'error': '权限不足'}), 403

    try:
        # 连接数据库
        connection = pymysql.connect(**db_config)
        with connection.cursor() as cursor:
            # 查询所有反馈
            sql = """
                SELECT * FROM user_feedback 
                ORDER BY created_at DESC  # 按创建时间倒序排列
            """
            cursor.execute(sql)
            feedbacks = cursor.fetchall()
        connection.close()

        return jsonify(feedbacks)

    except Exception as e:
        return jsonify({'error': '获取失败: {}'.format(str(e))}), 500


# 管理员获取反馈详情
@app.route('/api/feedback/admin/detail/<int:feedback_id>')
def get_feedback_detail(feedback_id):
    """获取指定反馈的详细信息
    - 验证管理员权限
    - 根据反馈ID查询详细信息
    - 返回反馈详情或错误信息
    """
    # 验证登录状态
    if 'logged_in' not in session or not session['logged_in']:
        return jsonify({'error': '未登录'}), 401

    # 验证管理员权限
    if session.get('user_type') != '管理员':
        return jsonify({'error': '权限不足'}), 403

    try:
        # 连接数据库
        connection = pymysql.connect(**db_config)
        with connection.cursor() as cursor:
            # 查询特定反馈
            sql = """
                SELECT * FROM user_feedback 
                WHERE id = %s  # 根据ID查询
            """
            cursor.execute(sql, (feedback_id,))
            feedback = cursor.fetchone()  # 获取单条结果
        connection.close()

        # 检查反馈是否存在
        if not feedback:
            return jsonify({'error': '反馈不存在'}), 404

        return jsonify(feedback)

    except Exception as e:
        return jsonify({'error': '获取失败: {}'.format(str(e))}), 500


# 管理员回复反馈
@app.route('/api/feedback/admin/respond', methods=['POST'])
def respond_to_feedback():
    """管理员回复用户反馈
    - 验证管理员权限
    - 更新反馈状态和回复内容
    - 创建用户通知
    - 记录系统日志
    - 通过UDP发送实时通知[1,6](@ref)
    """
    # 验证登录状态
    if 'logged_in' not in session or not session['logged_in']:
        return jsonify({'error': '未登录'}), 401

    # 验证管理员权限
    if session.get('user_type') != '管理员':
        return jsonify({'error': '权限不足'}), 403

    try:
        data = request.json
        feedback_id = data.get('feedback_id')  # 反馈ID
        admin_response = data.get('admin_response')  # 管理员回复内容

        # 获取客户端IP（用于日志记录）
        ip = request.remote_addr

        # 连接数据库
        connection = pymysql.connect(**db_config)
        with connection.cursor() as cursor:
            # 更新反馈状态和回复内容
            sql = """
                UPDATE user_feedback 
                SET status = 'responded',  # 更新状态为"已回复"
                admin_response = %s,       # 设置回复内容
                updated_at = CURRENT_TIMESTAMP  # 更新时间
                WHERE id = %s
            """
            cursor.execute(sql, (admin_response, feedback_id))

            # 获取反馈信息用于通知
            sql = """
                SELECT user_id, username, title, feedback_type, content 
                FROM user_feedback 
                WHERE id = %s
            """
            cursor.execute(sql, (feedback_id,))
            feedback = cursor.fetchone()

            # 创建用户通知
            sql = """
                INSERT INTO notifications (user_id, title, content)
                VALUES (%s, %s, %s)
            """
            cursor.execute(sql, (
                feedback['user_id'],  # 通知目标用户ID
                "反馈回复: {}".format(feedback['title']),  # 通知标题
                admin_response  # 通知内容
            ))

        connection.commit()  # 提交事务
        connection.close()  # 关闭连接

        # 记录系统日志（安全审计）
        insert_system_log(
            'INFO',  # 日志级别
            session.get('user_id'),  # 管理员ID
            session.get('username'),  # 管理员用户名
            'feedback_response',  # 操作类型
            '管理员回复反馈: {}'.format(feedback["title"]),  # 操作描述
            ip  # 客户端IP
        )

        # 通过UDP服务器发送实时通知[1,6](@ref)
        try:
            import socket
            import json

            # 创建UDP消息结构
            udp_message = {
                'type': 'admin_response_notification',  # 消息类型
                'data': {
                    'feedback_id': feedback_id,
                    'user_id': feedback['user_id'],
                    'username': feedback['username'],
                    'feedback_type': feedback['feedback_type'],
                    'feedback_title': feedback['title'],
                    'feedback_content': feedback['content'],
                    'admin_response': admin_response,
                    'admin_username': session.get('username'),  # 当前管理员
                    'timestamp': time.time()  # 时间戳
                }
            }

            # 使用UDP协议发送到本地9999端口[6](@ref)
            sock = socket.socket(socket.AF_INET, socket.SOCK_DGRAM)
            sock.sendto(json.dumps(udp_message).encode('utf-8'), ('localhost', 9999))
            sock.close()

            print("UDP反馈回复通知已发送到UDP服务器: {}".format(udp_message))

        except Exception as udp_error:
            print("UDP发送失败: {}".format(udp_error))

        return jsonify({'success': True, 'message': '回复成功'})

    except Exception as e:
        return jsonify({'error': '回复失败: {}'.format(str(e))}), 500


# 用户检查通知
@app.route('/api/notifications/check')
def check_notifications():
    """用户检查未读通知
    - 验证用户登录状态
    - 查询用户未读通知
    - 标记通知为已读
    - 格式化返回通知数据
    """
    if 'logged_in' not in session or not session['logged_in']:
        return jsonify({'error': '未登录'}), 401

    try:
        user_id = session.get('user_id')

        connection = pymysql.connect(**db_config)
        with connection.cursor() as cursor:
            # 查询用户未读通知（最多10条）
            sql = """
                SELECT * FROM notifications 
                WHERE user_id = %s AND is_read = 0  # 未读状态
                ORDER BY created_at DESC
                LIMIT 10
            """
            cursor.execute(sql, (user_id,))
            notifications = cursor.fetchall()

            # 标记为已读（原子操作）
            if notifications:
                notification_ids = [str(n['id']) for n in notifications]
                sql = """
                    UPDATE notifications 
                    SET is_read = 1  # 更新为已读状态
                    WHERE id IN ({})
                """.format(','.join(notification_ids))
                cursor.execute(sql)

        connection.commit()
        connection.close()

        # 格式化通知数据（适配前端）
        formatted_notifications = []
        for notification in notifications:
            formatted_notifications.append({
                'id': notification['id'],
                'type': 'feedback_response',  # 通知类型
                'data': {
                    'admin_response': notification.get('content', ''),
                    'timestamp': notification.get('created_at', '').isoformat()
                    if notification.get('created_at') else ''
                }
            })

        return jsonify({'notifications': formatted_notifications})

    except Exception as e:
        return jsonify({'error': '检查通知失败: {}'.format(str(e))}), 500


# UDP代理接口
@app.route('/api/udp-proxy', methods=['POST'])
def udp_proxy():
    """UDP代理接口
    - 前端与UDP服务器通信的桥梁[1](@ref)
    - 添加用户信息到消息中
    - 转发消息到本地UDP服务器
    - 提供安全隔离层
    """
    if 'logged_in' not in session or not session['logged_in']:
        return jsonify({'error': '未登录'}), 401

    try:
        data = request.json
        message = data.get('message')  # 原始UDP消息

        # 获取当前用户信息
        user_id = session.get('user_id')
        username = session.get('username')

        # 查询system_log表，获取该用户最近一条日志的IP
        client_ip = None
        try:
            connection = pymysql.connect(**db_config)
            with connection.cursor() as cursor:
                sql = "SELECT ip FROM system_log WHERE user_id=%s ORDER BY timestamp DESC LIMIT 1"
                cursor.execute(sql, (user_id,))
                row = cursor.fetchone()
                if row:
                    client_ip = row['ip']
        except Exception as e:
            print("查询IP失败:", e)
        finally:
            if 'connection' in locals():
                connection.close()

        # 添加用户信息和IP到消息体
        if message and 'data' in message:
            message['data']['user_id'] = user_id
            message['data']['username'] = username
            if client_ip:
                message['data']['client_ip'] = client_ip

        # 转发消息到UDP服务器
        import socket
        import json
        sock = socket.socket(socket.AF_INET, socket.SOCK_DGRAM)
        sock.sendto(json.dumps(message).encode('utf-8'), ('localhost', 9999))
        sock.close()

        print("UDP代理消息已转发: {}".format(message))
        return jsonify({'success': True, 'message': '消息已转发'})

    except Exception as e:
        return jsonify({'error': '转发失败: {}'.format(str(e))}), 500
############################################################


if __name__ == '__main__':
    app.run(debug=True, port=8000)
